﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using CruzDelSur.Dominio;
using CruzDelSur.Models;
using NHibernate;
using NHibernate.Criterion;

namespace CruzDelSur.Persistencia
{
    public class ReporteEspecialesDAO
    {
        public ICollection<ReportEspecialCantidadResult> reporteEspeciales(ReportesEspecialesModel model)
        {
            string codEstado = Servicio.ACTIVO;
            string query = "";
            if (model.FlagBaja) codEstado = Servicio.INACTIVO;
            if (model.FlagTodos) codEstado = "%";
            query += "CREATE TABLE #MyTempTable (campo1 char(20), campo2 char(10));" +
                     " insert into #MyTempTable select 'CONTRATO', COUNT(*) from SE_TB_Contrato WHERE (estContrato like :scodest);" +
                     " insert into #MyTempTable select 'CONVENIO', COUNT(*) from SE_TB_Convenio WHERE (estConvenio like :scodest);" +
                     " insert into #MyTempTable select 'PROMOCION', COUNT(*) from SE_TB_Promocion WHERE (estPromocion like :scodest);" +
                     " insert into #MyTempTable select 'PROYECTO ESPECIAL', COUNT(*) from SE_TB_Proyecto_Especial WHERE (estProyectoEspecial like :scodest);" +
                     " select * from #MyTempTable";
            using (ISession sesion = NHibernateHelper.AbrirSesion())
            {
                ICollection<ReportEspecialCantidadResult> resultado = sesion.CreateSQLQuery(query)
                .SetParameter("scodest", codEstado)
                .SetResultTransformer(new NHibernate.Transform.AliasToBeanResultTransformer(typeof(ReportEspecialCantidadResult)))
                .List<ReportEspecialCantidadResult>();
                return resultado;
            }
        }

        public ICollection<ReporteBarraEspecialResult> reporteEspecialGrafico(ReportesEspecialesModel model)
        {
            string codEstado = Servicio.ACTIVO;
            string query = "";
            if (model.FlagBaja) codEstado = Servicio.INACTIVO;
            if (model.FlagTodos) codEstado = "%";
            query += "CREATE TABLE #MyTempTable (Categoria char(20), Cantidad int);" +
                     " insert into #MyTempTable select 'CONTRATO', COUNT(*) from SE_TB_Contrato WHERE (estContrato like :scodest);" +
                     " insert into #MyTempTable select 'CONVENIO', COUNT(*) from SE_TB_Convenio WHERE (estConvenio like :scodest);" +
                     " insert into #MyTempTable select 'PROMOCION', COUNT(*) from SE_TB_Promocion WHERE (estPromocion like :scodest);" +
                     " insert into #MyTempTable select 'PROYECTO ESPECIAL', COUNT(*) from SE_TB_Proyecto_Especial WHERE (estProyectoEspecial like :scodest);" +
                     " select * from #MyTempTable";
            using (ISession sesion = NHibernateHelper.AbrirSesion())
            {
                ICollection<ReporteBarraEspecialResult> resultado = sesion.CreateSQLQuery(query)
                .SetParameter("scodest", codEstado)
                .SetResultTransformer(new NHibernate.Transform.AliasToBeanResultTransformer(typeof(ReporteBarraEspecialResult)))
                .List<ReporteBarraEspecialResult>();
                return resultado;
            }
        }
    }
}